//201902104003邵潇漩
package bysj.dao;
import bysj.domain.Department;
import bysj.domain.Major;
import bysj.service.DepartmentService;
import bysj.service.MajorService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

public final class MajorDao {
    private static MajorDao majorDao = null;

   /* static {
        DepartmentDao deptDao = DepartmentDao.getInstance();
        try {
            Department pmDepartment = deptDao.find(1);
            Department misDepartment = deptDao.find(2);
            Department pcDepartment = deptDao.find(3);
            Department ieDepartment = deptDao.find(4);

            majors = new TreeSet<Major>();
            Major major = new Major(1, "信息管理与信息系统", "020201", "", misDepartment);
            majors.add(major);
            majors.add(new Major(2, "工业工程", "020401", "", ieDepartment));
            majors.add(new Major(3, "工程管理", "020101", "", pmDepartment));
            majors.add(new Major(4, "工程造價", "020301", "", pcDepartment));
            majors.add(new Major(5, "信息系统项目管理", "020202", "", misDepartment));
        }catch (Exception e){
            e.printStackTrace();
        }
    }*/

    private MajorDao() {
    }

    public static MajorDao getInstance() {
        if (majorDao == null){
            majorDao = new MajorDao();
        }
        return majorDao;
    }

   /* private static void display(Collection<Major> majors) {
        for (Major major : majors) {
            System.out.println(major);
        }
    }*/

    public Collection<Major> findAll() throws SQLException {
        Collection<Major> desiredMajors=new TreeSet<Major>();
        Connection conn = JdbcHelper.getConn();
        String sql_select_all="SELECT*FROM major";
        PreparedStatement pstmt = conn.prepareStatement(sql_select_all);
        ResultSet resultSet=pstmt.executeQuery();
        while (resultSet.next()){
            int id=resultSet.getInt("id");
            String description=resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int departmentId=resultSet.getInt("department_id");
            Department department= DepartmentService.getInstance().find(departmentId);
            Major desiredMajor=new Major(id,description,no,remarks,department);
            desiredMajors.add(desiredMajor);
        }
        JdbcHelper.close(resultSet,pstmt,conn);
        return desiredMajors;
    }

    public Major find(Integer id) throws SQLException{
        Major desiredMajor = null;
        Connection conn=JdbcHelper.getConn();
        String sql_select_id="SELECT*FROM major WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql_select_id);
        pstmt.setInt(1,id);
        ResultSet resultSet=pstmt.executeQuery();
        if(resultSet.next()){
            String description=resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int departmentId=resultSet.getInt("department_id");
            Department department= DepartmentService.getInstance().find(departmentId);
            desiredMajor=new Major(id,description,no,remarks,department);
        }
        JdbcHelper.close(resultSet,pstmt,conn);
        return desiredMajor;
    }

    public boolean update(Major major) throws SQLException {
        //获得连接对象
        Connection conn=JdbcHelper.getConn();
        //创建sql语句
        String updateMajor_sql = "UPDATE major SET description=?, no=?, remarks=?, school_id=?WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateMajor_sql);
        // 为预编译参数赋值
        pstmt.setString(1,major.getDescription());
        pstmt.setString(2,major.getNo());
        pstmt.setString(3,major.getRemarks());
        pstmt.setInt(4,major.getDepartment().getId());
        pstmt.setInt(5,major.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt,conn);
        //返回school对象是否修改成功
        return affectedRowNum>0;
    }

    public boolean add(Major major) throws SQLException{
        Connection conn= JdbcHelper.getConn();
        String addMajor_sql="INSERT INTO major "+"(description,no,remarks,department_id)value(?,?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addMajor_sql);
        //为预编译参数赋值
        pstmt.setString(1, major.getDescription());
        pstmt.setString(2, major.getNo());
        pstmt.setString(3, major.getRemarks());
        pstmt.setInt(4,major.getDepartment().getId());
        int rowAffected =pstmt.executeUpdate();
        return rowAffected>0;
    }

    public boolean delete(Integer id) throws SQLException{
        //获得连接对象
        Connection conn=JdbcHelper.getConn();
        //创建sql语句
        String deleteMajor_sql = "DELETE FROM major WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteMajor_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt,conn);
        //如果删除记录的行数不为0，返回true,否则返回false
        return affectedRowNum>0;
    }

    public boolean delete(Major major) throws SQLException{
        return this.delete(major.getId());
    }
    public static void main(String[] args) throws SQLException{
        //从数据库中读取id=1的系记录，转换为内存对象
        //Department misDepartment=DepartmentDao.getInstance().find(1);
      //  System.out.println(misDepartment);
        //在内存中创建misMajor和pmMajor
        //Major misMajor=new Major("信息管理与信息系统","020201","",misDepartment);
        //Major pmMajor=new Major("工业工程","020401","",misDepartment);
        //将内存对象misMajor和pmMajor保存到数据库表中
        //MajorDao.getInstance().add(pmMajor);
        //MajorDao.getInstance().add(misMajor);
        //misMajor= MajorService.getInstance().find(1);
       // System.out.println(misMajor);
        MajorDao.getInstance().delete(2);
        Collection<Major> majors=MajorDao.getInstance().findAll();
        System.out.println(majors);
    }
}
